
package hk.ces.oxfam.patch.transaction;

import hk.ces.oxfam.patch.TransactionPatch;
import hk.ces.oxfam.patch.TransactionStrategy;
import hk.ces.oxfam.patch.TransactionStrategyType;
import hk.ces.oxfam.patch.db.SQLUtil;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.db.sql.PreparedSQL;
import hk.ces.oxfam.patch.sql.ExcuteUtil;
import hk.ces.oxfam.patch.util.CalendarUtil;
import hk.ces.oxfam.patch.xml.SQLResultXML;
import hk.ces.oxfam.patch.xml.factory.SQLResultXMLFactory;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import org.apache.log4j.Logger;

/**
 *
 * @author ReutyCheng
 */
@TransactionStrategy(TransactionStrategyType.COMMIT_IN_ENGINE)
public class FixReceiptNoPatch implements TransactionPatch
{
  // Production
  private static final  String[] PROBLEM_RECEIPT_NOS = {
//    "TWFL2011153%",
    "TWFL201110041675"
  };

  private static final java.sql.Date APRIL_ONE = new java.sql.Date(CalendarUtil.getDate(2011, 3, 1).getTime());
  private static final Object[][] ROLLBACK_RECEIPT_NOS_PARAMS =
  {
    {"TWFL20111530041058", 1, APRIL_ONE, 54265L},
    {"TWFL20111530041059", 1, APRIL_ONE, 54266L},
    {"TWFL20111530041060", 1, APRIL_ONE, 54267L},
    {"TWFL20111530041049", 1, APRIL_ONE, 54268L},
    {"TWFL20111530041050", 1, APRIL_ONE, 54269L},
    {"TWFL20111530041051", 1, APRIL_ONE, 54270L},
    {"TWFL20111530041052", 1, APRIL_ONE, 54271L},
    {"TWFL20111530041053", 1, APRIL_ONE, 54272L},
    {"TWFL20111530041054", 1, APRIL_ONE, 54273L},
    {"TWFL20111530041055", 1, APRIL_ONE, 54274L},
    {"TWFL20111530041056", 1, APRIL_ONE, 54275L},
    {"TWFL20111530041057", 1, APRIL_ONE, 54276L}
  };
  private static final Object[] ROLLBACK_TRANSACTIONS_PARAMES =
  {
    54385L,
    54386L,
    54387L,
    54388L,
    54389L,
    54390L,
    54391L,
    54392L,
    54393L,
    54394L,
    54395L,
    54396L
  };
  // Local_4
//  private static final String PROBLEM_RECEIPT_NO = "TWFL201010000016";

  public void run(Connection connection) throws SQLException
  {
    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
    Logger.getLogger(this.getClass()).debug(dateFormat.format(APRIL_ONE));
    for (String problemedReceiptNo : PROBLEM_RECEIPT_NOS)
    {
      if (problemedReceiptNo == null)
      {
        continue;
      }
      
      SQLResultXMLFactory factory = new SQLResultXMLFactory("problemReceipt_" + problemedReceiptNo);
      factory.setOverwrite(true);
      factory.setXMLItems(new SQLResultXML(this.getProblemedReceipts(connection, problemedReceiptNo)), new SQLResultXML(this.getProblemedTransaction(connection, problemedReceiptNo)));
      factory.writeToFile();

      int result = 0;
      result = this.unlockTransactions(connection, problemedReceiptNo);
      Logger.getLogger(this.getClass()).debug("Unlock Transaction[count=" + result + "]");
      result = this.clearReceiptNoInReceipts(connection, problemedReceiptNo);
      Logger.getLogger(this.getClass()).debug("Clear Receipt No. and clear PrintCount[count=" + result + "]");
    }

    for (Object[] rollbackReceiptNosParam : ROLLBACK_RECEIPT_NOS_PARAMS)
    {
      if (rollbackReceiptNosParam == null)
      {
        continue;
      }
      
      this.rollbackReceiptNoInReceipts(connection, rollbackReceiptNosParam);
      Logger.getLogger(this.getClass()).debug("Rollback Receipt[" + rollbackReceiptNosParam + "]");
    }

    for (Object rollbackTransactionsParam : ROLLBACK_TRANSACTIONS_PARAMES)
    {
      if (rollbackTransactionsParam == null)
      {
        continue;
      }
      
      this.lockTransactions(connection, (Long) rollbackTransactionsParam);
      Logger.getLogger(this.getClass()).debug("Lock Transaction[" + rollbackTransactionsParam + "]");
    }
  }

  private SQLResult getProblemedReceipts(Connection connection, String receiptNo) throws SQLException
  {
    String sql = "select * from receipt where receiptno LIKE ? and receiptstate != ? and disabled = ?";
    Object[] params = new Object[] {receiptNo, "VOID", false};
    return SQLUtil.query(connection, new PreparedSQL(sql, params));
  }

  private SQLResult getProblemedTransaction(Connection connection, String receiptNo) throws SQLException
  {
    String sql = "select * from transaction where receipt_id in (select id from receipt where receiptno LIKE ? and receiptstate != ? and disabled = ?) and disabled = ?";
    Object[] params = new Object[] {receiptNo, "VOID", false, false};
    return SQLUtil.query(connection, new PreparedSQL(sql, params));
  }

  private int unlockTransactions(Connection connection, String receiptNo) throws SQLException
  {
    String sql = "update transaction set locked = ? where receipt_id in (select id from receipt where receiptno LIKE ? and receiptstate != ? and disabled = ?) and disabled = ?";
    Object[] params = new Object[] {false, receiptNo, "VOID", false, false};
    return ExcuteUtil.excute(connection, new hk.ces.oxfam.patch.sql.PreparedSQL(sql, params));
  }

  private int clearReceiptNoInReceipts(Connection connection, String receiptNo) throws SQLException
  {
    String sql = "update receipt set receiptno = ?, printcount = ?, firstprintdate = ? where receiptno LIKE ? and receiptstate != ? and disabled = ?";
    Object[] params = new Object[] {null, 0, null, receiptNo, "VOID", false};
    return ExcuteUtil.excute(connection, new hk.ces.oxfam.patch.sql.PreparedSQL(sql, params));
  }

  private int lockTransactions(Connection connection, long transactionId) throws SQLException
  {
    String sql = "update transaction set locked = ? where receipt_id = ?";
    Object[] params = new Object[] {true, transactionId};
    return ExcuteUtil.excute(connection, new hk.ces.oxfam.patch.sql.PreparedSQL(sql, params));
  }

  private int rollbackReceiptNoInReceipts(Connection connection, Object[] parameters) throws SQLException
  {
    String sql = "update receipt set receiptno = ?, printcount = ?, firstprintdate = ? where id = ?";
    return ExcuteUtil.excute(connection, new hk.ces.oxfam.patch.sql.PreparedSQL(sql, parameters));
  }
}
